using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using DevComponents.DotNetBar.Metro;
using OfficeOpenXml;
using PlatForm.DataLayer;

namespace PlatForm.Presentation
{
    public partial class frmOrderImport : MetroForm
    {
        private List<PickingOrder> lstPO = new List<PickingOrder>();
        private List<Order> lstOrder = new List<Order>();
        private List<Article> lstArticle = new List<Article>();
        private List<Article> availableArt = new List<Article>();
        private List<Supplier> lstSupplier  =new List<Supplier>();
        private List<Supplier> availableSupp = new List<Supplier>();

        public frmOrderImport()
        {
            InitializeComponent();
            dgvPO.AutoGenerateColumns = false;
            dgvOrder.AutoGenerateColumns = false;
            dgvArticle.AutoGenerateColumns = false;
            availableArt = Article.Objects.GetAll();
            availableSupp = Supplier.Objects.GetAll();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            var x = openFileDialog1.ShowDialog();
            if (x == DialogResult.OK)
            {
                txtFileName.Text = openFileDialog1.FileName;
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (txtFileName.Text.Trim() == "")
            {
                MessageBox.Show("Please input filename");
            }
            else
            {
                try
                {
                    var fileName = txtFileName.Text;
                    const int startRow = 1;
                    
                    var lastPONo = "";

                    // Get the file we are going to process
                    var existingFile = new FileInfo(fileName);
                    // Open and read the XlSX file.
                    using (var package = new ExcelPackage(existingFile))
                    {
                        // Get the work book in the file
                        ExcelWorkbook workBook = package.Workbook;
                        if (workBook != null)
                        {
                            if (workBook.Worksheets.Count > 0)
                            {
                                // Get the first worksheet
                                ExcelWorksheet currentWorksheet = workBook.Worksheets.First();


                                for (int rowNumber = startRow + 1;
                                     rowNumber <= currentWorksheet.Dimension.End.Row;
                                     rowNumber++)
                                    // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                                {
                                    if (currentWorksheet.Cells[rowNumber, 2].Value != null && lstPO.FirstOrDefault(x => x.PO_No == currentWorksheet.Cells[rowNumber, 2].Value.ToString())==null)
                                    {
                                        if (currentWorksheet.Cells[rowNumber, 3].Value != null &&
                                            availableSupp.FirstOrDefault(
                                                x => x.SUPPL_No == currentWorksheet.Cells[rowNumber, 3].Value.ToString()) ==
                                            null)
                                        {
                                            lstSupplier.Add(new Supplier()
                                            {
                                                SUPPL_No = currentWorksheet.Cells[rowNumber, 3].Value.ToString(),
                                                SUPPL_Name = currentWorksheet.Cells[rowNumber, 4].Value.ToString()
                                            });
                                        }

                                        lstPO.Add(new PickingOrder()
                                                      {
                                                          WH_No =
                                                              int.Parse(currentWorksheet.Cells[rowNumber, 1].Value == null
                                                                            ? "79"
                                                                            : currentWorksheet.Cells[rowNumber, 1].Value
                                                                                  .ToString()),
                                                          PO_No = currentWorksheet.Cells[rowNumber, 2].Value.ToString(),
                                                          SUPPL_No = currentWorksheet.Cells[rowNumber, 3].Value.ToString(),
                                                          SUPPL_Name = currentWorksheet.Cells[rowNumber, 4].Value.ToString(),
                                                          ORD_Date = DateTime.ParseExact(
                                                              currentWorksheet.Cells[rowNumber, 5].Value.ToString(),
                                                              "d-M-yyyy", CultureInfo.InvariantCulture),
                                                          DELI_Date_WH = DateTime.ParseExact(
                                                              currentWorksheet.Cells[rowNumber, 6].Value.ToString(),
                                                              "d-M-yyyy", CultureInfo.InvariantCulture),
                                                      });
                                        lastPONo = currentWorksheet.Cells[rowNumber, 2].Value.ToString();

                                    }else
                                    {
                                        if (currentWorksheet.Cells[rowNumber, 7].Value != null && lstOrder.FirstOrDefault(x => x.ORD_No == currentWorksheet.Cells[rowNumber, 9].Value.ToString()) == null)
                                        {
                                            lstOrder.Add(new Order()
                                                             {
                                                                 PO_No = lastPONo,
                                                                 ST_No = currentWorksheet.Cells[rowNumber, 7].Value.ToString(),
                                                                 DELI_QTY = float.Parse(currentWorksheet.Cells[rowNumber, 8].Value.ToString()),
                                                                 ORD_No = currentWorksheet.Cells[rowNumber, 9].Value.ToString(),
                                                                 ART_No = currentWorksheet.Cells[rowNumber, 10].Value.ToString(),
                                                                 ART_Name = currentWorksheet.Cells[rowNumber, 11].Value.ToString(),
                                                                 DELI_Date = DateTime.ParseExact(currentWorksheet.Cells[rowNumber, 12].Value.ToString(),"d-M-yyyy",CultureInfo.InvariantCulture),
                                                             });
                                        }


                                        if (currentWorksheet.Cells[rowNumber, 10].Value != null &&
                                            availableArt.FirstOrDefault(
                                                x => x.ART_No == currentWorksheet.Cells[rowNumber, 10].Value.ToString()) ==
                                            null)
                                        {
                                            lstArticle.Add(new Article()
                                                {
                                                    ART_No = currentWorksheet.Cells[rowNumber, 10].Value.ToString(),
                                                    ART_Name = currentWorksheet.Cells[rowNumber, 11].Value.ToString(),
                                                    LastUpdated = DateTime.Now,
                                                    Cat = 1,
                                                    Unit = 0
                                                });
                                        }

                                    }
                                }
                                Supplier.Objects.Insert(lstSupplier);
                                dgvPO.DataSource = lstPO.Distinct().ToList();
                                dgvArticle.DataSource = lstArticle.Distinct().ToList();
                            }
                        }
                    }

                }
                catch (IOException ioEx)
                {
                    if (!String.IsNullOrEmpty(ioEx.Message))
                    {
                        if (ioEx.Message.Contains("because it is being used by another process."))
                        {
                            MessageBox.Show("Could not read example data. Please make sure it not open in Excel.");
                        }
                    }
                    MessageBox.Show("Could not read example data. " + ioEx.Message);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("An error occured while reading example data. " + ex.Message);
                }
            }
        }

        private void dgvPO_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            dgvOrder.DataSource = lstOrder.Where(x => dgvPO.CurrentRow != null && x.PO_No == dgvPO.CurrentRow.Cells[1].Value.ToString()).ToList();
        }

        private void btnSaveAll_Click(object sender, EventArgs e)
        {
            PickingOrder.Objects.Insert(lstPO);
            Order.Objects.Insert(lstOrder);
        }

    }
}